﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Timers;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;

namespace SmsSyncService
{
    public class SmsSyncTask
    {
        private Timer _smsSyncTask;
        private string siteDbConStr;
        private string smsDbConStr;

        public SmsSyncTask()
        {
            siteDbConStr = Properties.Settings.Default.SiteDbConStr;
            siteDbConStr = string.Format(siteDbConStr,
                WebUI.OtherSettings.Values["s228470/user"],
                WebUI.OtherSettings.Values["s228470/pwd"]);

            smsDbConStr = Properties.Settings.Default.SmsDbConStr;
        }

        public void Start()
        {
            int sendSec = Properties.Settings.Default.SendTimeSecond;
            _smsSyncTask = new Timer(sendSec * 1000);
            //_smsSyncTask = new Timer(1);
            _smsSyncTask.Elapsed += new ElapsedEventHandler(_smsSyncTask_Elapsed);
            _smsSyncTask.AutoReset = true;
            _smsSyncTask.Enabled = true;
        }

        public void End()
        {
            _smsSyncTask.Stop();
        }

        private void _smsSyncTask_Elapsed(object sender, ElapsedEventArgs e)
        {
            try
            {
                if (TryReadSmsDB())
                {
                    List<SmsModel> receviedList = GetSmsMessageList();
                    if (WriteToSitDB(receviedList))
                    {
                        UpdateSMSDB(receviedList);
                    }
                    List<SmsModel> smsList = GetSiteMessageList();
                    if (smsList != null && smsList.Count > 0)
                    {
                        WriteToSmsDB(smsList);
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private bool TryReadSmsDB()
        {
            try
            {
                string cmdStr = "select top 1 * from dbo.OutBox";
                using (SqlConnection conn = new SqlConnection(smsDbConStr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(cmdStr, conn);
                    cmd.ExecuteReader();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }

        }

        private List<SmsModel> GetSiteMessageList()
        {
            List<SmsModel> smsList = new List<SmsModel>();
            string cmdStr = string.Empty;
            using (SqlConnection conn = new SqlConnection(siteDbConStr))
            {
                conn.Open();
                string cmdTemplate = "UPDATE SmsSent SET ProcStatus='已发送' WHERE ID = '{0}'; ";
                SqlCommand cmd = new SqlCommand("Select ID,ReceiverCode,ReceiverName,TextMessage"
                + " from SmsSent where ProcStatus='发送';", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    SmsModel sms = new SmsModel();
                    sms.ID = reader.GetString(0);
                    sms.Mbno = reader.GetString(1);
                    sms.UserName = reader.GetString(2);
                    sms.Msg = reader.GetString(3);
                    smsList.Add(sms);
                    cmdStr += string.Format(cmdTemplate, sms.ID);
                }
            }
            using (SqlConnection conn = new SqlConnection(siteDbConStr))
            {
                conn.Open();
                if (!string.IsNullOrEmpty(cmdStr))
                {
                    SqlTransaction tran = conn.BeginTransaction();
                    try
                    {
                        SqlCommand updateCmd = new SqlCommand(cmdStr, conn, tran);
                        updateCmd.ExecuteNonQuery();
                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        return null;
                    }
                }

            }
            return smsList;
        }

        private bool WriteToSmsDB(List<SmsModel> smsList)
        {
            string cmdStr = string.Empty;
            string cmdTemplate = "Insert Into dbo.OutBox (Mbno,UserName,Msg) Values('{0}','{1}','{2}');";
            foreach (var sms in smsList)
            {
                cmdStr += string.Format(cmdTemplate, sms.Mbno, sms.UserName, sms.Msg);

            }
            using (SqlConnection conn = new SqlConnection(smsDbConStr))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(cmdStr, conn, tran);
                try
                {
                    cmd.ExecuteNonQuery();
                    tran.Commit();
                    Console.WriteLine("Send Succ");
                    return true;
                }
                catch
                {
                    tran.Rollback();
                    Console.WriteLine("Send Failed");
                    return false;
                }
            }
        }

        private List<SmsModel> GetSmsMessageList()
        {
            List<SmsModel> smsList = new List<SmsModel>();
            string cmdStr = string.Empty;
            using (SqlConnection conn = new SqlConnection(smsDbConStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("Select ID,mbno,Msg,ArriveTime"
                + " from dbo.InBox where Readed='0';", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    SmsModel sms = new SmsModel();
                    sms.ID = reader.GetInt32(0).ToString();
                    sms.Mbno = reader.GetString(1);
                    sms.Msg = reader.GetString(2);
                    sms.ArrivedTime = reader.GetDateTime(3);
                    smsList.Add(sms);
                }
            }
            return smsList;
        }

        private bool UpdateSMSDB(List<SmsModel> smsList)
        {
            string cmdStr = string.Empty;
            string cmdTemplate = "UPDATE dbo.InBox SET Readed='1' WHERE ID = '{0}'; ";
            foreach (var sms in smsList)
            {
                cmdStr += string.Format(cmdTemplate, sms.ID, sms.Mbno, string.Empty, sms.Msg, sms.ArrivedTime, sms.ArrivedTime, "已接收", string.Empty, string.Empty);
            }
            using (SqlConnection conn = new SqlConnection(smsDbConStr))
            {
                conn.Open();
                if (!string.IsNullOrEmpty(cmdStr))
                {
                    SqlTransaction tran = conn.BeginTransaction();
                    try
                    {
                        SqlCommand updateCmd = new SqlCommand(cmdStr, conn, tran);
                        updateCmd.ExecuteNonQuery();
                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }

        private bool WriteToSitDB(List<SmsModel> smsList)
        {
            string cmdStr = string.Empty;
            string cmdTemplate = "Insert Into SmsReceived (ID,ReceiverCode,SenderCode,TextMessage,SentTime,ProcTime,ProcStatus,ReceiverName,SenderName)"
                + " Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');";
            foreach (var sms in smsList)
            {
                cmdStr += string.Format(cmdTemplate, sms.ID, string.Empty, sms.Mbno, sms.Msg.Replace("'", "''"), sms.ArrivedTime, sms.ArrivedTime, "已接收", string.Empty, string.Empty);
            }
            using (SqlConnection conn = new SqlConnection(siteDbConStr))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(cmdStr, conn, tran);
                try
                {
                    cmd.ExecuteNonQuery();
                    tran.Commit();
                    return true;
                }
                catch
                {
                    tran.Rollback();
                    return false;
                }
            }
        }
    }
}
